<?php
/* explain:		news system
 * Project:     
 * File:        news.php
 *
 * @ link 		http://www.../modules/
 * @ Email		ldmmyx@hotmail.com
 * @ copyright 	2005  Ling Deming
 * @ author 	ivan.ling  Ling Deming
 * @ version 	1.0
 *
 */
include_once("utils/CommonUtils.php");
include_once("domain/ImageDomain.php");
include_once("domain/MerchantDomain.php");
include_once("domain/AttributeDomain.php");
include_once("dao/CommonDAO.php");
include_once("dao/MerchantDAO.php");

class MerchantImpl extends CommonUtils implements MerchantDAO, CommonDAO
{
	function __construct($DBTag)
	{
		parent::__construct();
		
		$this->DBTag 	= $DBTag;		
	}

	/* get data via ID
	 *
	 * @param  int    id
	 * @return mixed  false:
	 */
	public function getById($id) 
	{
		$sql  = 'SELECT name, nameId, displayURL, descript, isFeatured, keywords ';
		$sql .= 'FROM merchant WHERE id='.$id;
		
		$oMer = null;
		$conn = null;
		
		try {
			$conn = $this->getConnection($this->DBTag);
			
			$rs = mysql_query($sql, $conn);
			if ($row = mysql_fetch_row($rs)) {
				$oMer = new MerchantDomain();

				$oMer->setId($id);
				$oMer->setName($row[0]);
				$oMer->setNameId($row[1]);
				$oMer->setDisplayURL($row[2]);
				$oMer->setDescript(strip_tags($row[3]));
				$oMer->setFeatured($row[4]);
				$oMer->setKeywords($row[5]);
				
			} else {
				$this->message = "get merchant data failed by Id. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($conn);
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
			
		return $oMer;
	}
	
	public function getArrayById($id)
	{
		$sql = "SELECT * FROM merchant WHERE id = $id";
		
		return $this->getRow($this->DBTag, $sql);
	}

	/* get data via name id
	 *
	 * @param  int    id
	 * @return mixed  false:
	 */
	public function getByNameId($id) 
	{
		$sql  = 'SELECT id, name, nameId, displayURL, descript, isFeatured ';
		$sql .= "FROM merchant WHERE nameId='$id'";
		$conn = null;
		$oMer = null;
		
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn);
			if ($row = mysql_fetch_row($rs)) {
				$oMer = new MerchantDomain();

				$oMer->setId($row[0]);
				$oMer->setName($row[1]);
				$oMer->setNameId($row[2]);
				$oMer->setDisplayURL($row[3]);
				$oMer->setDescript(strip_tags($row[4]));
				$oMer->setFeatured($row[5]);				
				
			} else {
				$this->message = "get merchant data failed by Id. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($conn);
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		return $oMer;
	}
	
	/* get data via name id
	 *
	 * @param  int    id
	 * @return mixed  false:
	 */
	public function getByName($name) 
	{
		$sql  = 'SELECT id, name, nameId, displayURL, descript, isFeatured ';
		$sql .= "FROM merchant WHERE name='" . addslashes($name) . "'";
		$conn = null;
		$oMer = null;
		
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn);
			if ($row = mysql_fetch_row($rs)) {
				$oMer = new MerchantDomain();

				$oMer->setId($row[0]);
				$oMer->setName($row[1]);
				$oMer->setNameId($row[2]);
				$oMer->setDisplayURL($row[3]);
				$oMer->setDescript(strip_tags($row[4]));
				$oMer->setFeatured($row[5]);				
				
			} else {
				$this->message = "get merchant data failed by name. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($conn);
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		return $oMer;
	}
	
	public function getByAlias($alias) 
	{
		$sql = "SELECT merchantId FROM merchant_alias WHERE alias='" . addslashes($alias) . "'";
		
		$mercahntId = $this->getColumn($this->DBTag, $sql);
		
		if(empty($mercahntId)){
			return false;
		}else{
			return $this->getById($mercahntId);
		}
	}
	
	public function getByDisplayUrl($url) 
	{
		if(empty($url))
			return false;
			
		$sql = "SELECT id FROM merchant WHERE displayURL='" . addslashes($url) . "'";
		
		$mercahntId = $this->getColumn($this->DBTag, $sql);
		
		if(empty($mercahntId)){
			return false;
		}else{
			return $this->getById($mercahntId);
		}
	}
	
	public function getAdvertiserId($merId) 
	{
		$sql = "SELECT attributeValue FROM merchant_attributes WHERE attributeId = 5 AND merchantId = $merId";
		
		$advertiserId = $this->getColumn($this->DBTag, $sql);
		
		if(empty($advertiserId)){
			return false;
		}else{
			return $advertiserId;
		}
	}
	
	/* get merchant object via advertiser Id
	 *
	 * @param  int    id
	 * @return mixed  false:
	 */
	public function getAdvertiserCategory($sourceId, $advertiserId) 
	{
		$sql  	= "SELECT category FROM advertiser_category_log WHERE advertiserId = '$advertiserId' AND sourceId = '$sourceId'";
		
		return $this->getColumn($this->DBTag, $sql);
	}
	
	public function getByAdvertiserId($sourceId, $advertiserId)
	{
		$sql  		= "SELECT merchantId FROM merchant_attributes WHERE attributeId = 5 AND sourceId = $sourceId AND attributeValue = '$advertiserId'";
		
		$merchantId = $this->getColumn($this->DBTag, $sql);
		
		if(!empty($merchantId)){
			return $this->getById($merchantId);
		}else{
			return false;
		}
	}
	
	public function add($oMer)
	{		
		if($oMer instanceof MerchantDomain){
			$mer = $this->getByNameId($oMer->getNameId());
			if(is_object($mer)){
				$oMer->setId($mer->getId());
				
				return $mer->getId();
			}
			
			
			$sql 	 = "INSERT INTO merchant SET createdTime = UTC_TIMESTAMP(), descript='";
			$sql	.= addslashes($oMer->getDescript());
			
			$sql 	.= "', name='";
			$sql	.= addslashes($oMer->getName());
			
			$sql 	.= "', nameId='";
			$sql	.= addslashes($oMer->getNameId());	
	
			$sql 	.= "', displayURL='";
			$sql	.= addslashes($oMer->getdisplayURL());
	
			$sql 	.= "', trackingURL='";
			$sql	.= $oMer->getTrackingURL();

			$sql	.= "', CSPhone='";
			$sql	.= $oMer->getCSPhone();

			$sql	.= "', CSEmail='";
			$sql	.= $oMer->getCSEmail();

			$sql	.= "', status='";
			$sql	.= $oMer->getStatus();
			
			$sql	.= "', r_nameFirstLetter='";
			$sql	.= $oMer->getNameFirstLetter();
			
			$sql	.= "' ON DUPLICATE KEY UPDATE trackingURL=Values(trackingURL), displayURL=Values(displayURL), CSPhone=Values(CSPhone), " .
					"CSEmail=Values(CSEmail), descript=Values(descript), r_nameFirstLetter=Values(r_nameFirstLetter)";
			
			$id = $this->insert($this->DBTag, $sql);
			
			$oMer->setId($id);		
		}else{
			$id	= -1;
			
			$this->message = 'input object is not instance of MerchantDomain.';
		}
		
		
		return $id;
	}
	
	public function edit($oMer)
	{		
		if($oMer instanceof MerchantDomain){
			$sql 	 = "UPDATE merchant SET createdTime = UTC_TIMESTAMP(), descript='";
			$sql	.= addslashes($oMer->getDescript());
			
			$sql 	.= "', name='";
			$sql	.= addslashes($oMer->getName());
			
			$sql 	.= "', nameId='";
			$sql	.= addslashes($oMer->getNameId());	
	
			$sql 	.= "', displayURL='";
			$sql	.= addslashes($oMer->getdisplayURL());
	
			$sql 	.= "', trackingURL='";
			$sql	.= $oMer->getTrackingURL();

			$sql	.= "', CSPhone='";
			$sql	.= $oMer->getCSPhone();

			$sql	.= "', CSEmail='";
			$sql	.= $oMer->getCSEmail();

			$sql	.= "', status='";
			$sql	.= $oMer->getStatus();
			
			$sql	.= "', r_nameFirstLetter='";
			$sql	.= $oMer->getNameFirstLetter();
			
			$sql	.= "' WHERE id = ";
			$sql	.= $oMer->getId();
			
			return $this->update($this->DBTag, $sql);			
		}else{
			$this->message = 'input object is not instance of MerchantDomain.';
		}
			
		return false;
	}
	
	public function addAttribute($sourceId, $merchantId, $attributeId, $attributeValue)
	{
		$sql_data_array = array(
			'merchantId' 		=> $merchantId,
			'sourceId' 			=> $sourceId,
			'attributeId' 		=> $attributeId,
			'attributeValue' 	=> $attributeValue
		);

		return $this->insertArray($this->DBTag, 'merchant_attributes', $sql_data_array, " ON DUPLICATE KEY UPDATE sourceId=Values(sourceId), attributeValue=Values(attributeValue), modifiedTime=Values(modifiedTime)");
	}
	
	public function addAlias($merchantId, $merchantName)
	{
		$sql_data_array = array(
			'alias' 	=> $merchantName,
			'merchantId' 	=> $merchantId
		);
				
		$this->insertArray($this->DBTag, 'merchant_alias', $sql_data_array, " ON DUPLICATE KEY UPDATE merchantId=Values(merchantId)");
	}
	
	public function addAdvertiserCategory($sourceId, $advertiserId, $category)
	{
		$sql_data_array = array(
			'advertiserId' 	=> $advertiserId,
			'sourceId' 	=> $sourceId,
			'category' 	=> $category
		);
				
		$this->insertArray($this->DBTag, 'advertiser_category_log', $sql_data_array, " ON DUPLICATE KEY UPDATE modifiedTime=Values(modifiedTime)");
	}
	
	public function addLogo($merchantId, $imageId, $level = 0)
	{
		$sql_data_array = array(
			'merchantId' 	=> $merchantId,
			'imageId' 		=> $imageId,
			'level' 		=> $level,
		);
				
		$this->insertArray($this->DBTag, 'merchant_images', $sql_data_array, " ON DUPLICATE KEY UPDATE level=Values(level)");
	}
	
	public function addCategory($merchantId, $categoryId)
	{
		$sql_data_array = array(
			'merchantId' 	=> $merchantId,
			'categoryId' 	=> $categoryId
			
		);
				
		$this->insertArray($this->DBTag, 'merchant_category', $sql_data_array, " ON DUPLICATE KEY UPDATE merchantId=Values(merchantId)");
	}
	
	public function addSource($merchantId, $sourceId)
	{
		$sql_data_array = array(
			'merchantId' 	=> $merchantId,
			'sourceId' 	=> $sourceId
			
		);
				
		$this->insertArray($this->DBTag, 'merchant_source', $sql_data_array, " ON DUPLICATE KEY UPDATE merchantId=Values(merchantId)");
	}
	
	/* delete news class
	 *
	 * @param  int    	id
	 * @param  string  	status
	 * @param  return 	boolean
	 */
	public function delete($id)
	{
		$sql = "DELETE FROM merchant WHERE id = $id";
		
		return $this->update($this->DBTag, $sql);
	}
	
	public function deleteLogo($merchantID, $imageID)
	{
		$sql = "DELETE FROM merchant_images WHERE merchantID = '$merchantID' AND imageID='$imageID'";

		return $this->update($this->DBTag, $sql);
	}
	
	public function getPopular($page = 0, $discount = 20)
	{
		
		$where	= ' WHERE status = 1';
		$order 	= ' ORDER BY r_popularity DESC';
		
		$oMerchants = $this->getCustoms($where, $order, $page, $discount);
		
		foreach($oMerchants as $oMerchant){
			$this->getAttributes($oMerchant);
			
//			echo "attriubtes : " . $oMerchant->getAttribute(1, 52)->getAttributeValue() . "<br>";
		}
		
		return $oMerchants;
	}
	
	public function getPopularByRelatedCategory($merId, $page = 0, $discount = 20)
	{
		$sql 		= "SELECT GROUP_CONCAT(categoryId) FROM merchant_category WHERE merchantId = $merId GROUP BY merchantId";
		$catIdList 	= $this->getColumn($this->DBTag, $sql);
		
		if(empty($catIdList)){
			return array();
		}else{
			$where	= " WHERE status = 1 AND id in (SELECT merchantId FROM merchant_category WHERE categoryId IN ($catIdList))";
			$order 	= ' ORDER BY r_popularity DESC';
			
			return $this->getCustoms($where, $order, $page, $discount);			
		}
	}
	
	public function getPopularByCategoryId($categoryId, $page = 0, $discount = 20)
	{
		$where	= " WHERE status = 1 AND id in (SELECT merchantId FROM merchant_category WHERE categoryId = $categoryId)";
		$order 	= ' ORDER BY r_popularity DESC';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getPopularByTagId($tagId, $page = 0, $discount = 20)
	{
		$where	= " WHERE status = 1 AND id in (SELECT merchantId FROM merchant_tag WHERE authorized = 1 AND tagId IN ($tagId))";
		$order 	= ' ORDER BY r_popularity DESC';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getFeaturedMerchants($firstLetter, $page = 0, $discount = 20)
	{
		$where	= " WHERE status = 1 AND isFeatured = 1 AND r_nameFirstLetter = '$firstLetter'";
		$order 	= '';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getAllFeaturedMerchants($page = 0, $discount = 20)
	{
		$where	= ' WHERE status = 1 AND isFeatured = 1';
		$order 	= '';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getRecommended($page = 0, $discount = 20)
	{
		$where	= ' WHERE status = 1 AND isRecommended = 1';
		$order 	= '';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getMerchantsByFirstLetter($firstLetter)
	{
		$where	= " WHERE status = 1 AND r_CouponCount > 0 AND r_nameFirstLetter = '$firstLetter'";
		$order 	= ' ORDER BY name';
		
		return $this->getCustoms($where, $order, 0, 0);
	}

	/* merchant list
	 *
	 * @ param  return 	array
	 */

	public function getValidAllMerchants()
	{
		$where	= ' WHERE status = 1 AND r_CouponCount > 0 ';
		$order 	= '';
		
		return $this->getCustoms($where, $order, 0, 0);
	}
	
	/* list
	 *
	 * @ param  string 	class id
	 * @ param  string 	page
	 * @ param  string 	discount
	 * @ param  string 	url
	 * @ param  return 	array
	 */
	public function getCustoms($where, $order, $page = 0, $discount = 20)
	{
		$arrback 	= array();
		$conn		= null;
		
		try{
			$conn 	= $this->getConnection($this->DBTag);
			$sql 	= 'SELECT count(1) FROM merchant' . $where;
			
			$rs  	= mysql_query($sql, $conn);
			
			list($total) = mysql_fetch_row($rs);
			
			$this->mutilPager($total, $page, $discount);
			
			if(!empty($total)){
				$sql  = 'SELECT id, name, nameId, displayURL, descript, r_CouponCount, isFeatured, keywords ';
				$sql .= "FROM merchant $where $order";
				
				if($discount > 0)
					$sql .= " LIMIT $this->firstcount, $discount";
				
				$rs  = mysql_query($sql, $conn);
				if($rs){
					while($row = mysql_fetch_array($rs)){
						$oMer = new MerchantDomain();

						$oMer->setId($row[0]);
						$oMer->setName($row[1]);
						$oMer->setNameId($row[2]);
						$oMer->setDisplayURL($row[3]);
						$oMer->setDescript(strip_tags($row[4]));
						$oMer->setCouponCount($row[5]);
						$oMer->setFeatured($row[6]);
						$oMer->setKeywords($row[7]);
						
						$arrback[] = $oMer;
					}
				}else {
					$this->message = "select failed. sql : $sql";
					$this->message .= "<br>error: " . mysql_error($conn);
				}
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();			
		}
		
		$this->freeConnection($this->DBTag, $conn);	
		
		return $arrback;
	}
	
	public function getCustomArray($where, $orders, $page = 0, $discount = 20, $url = '')
	{
		return $this->getPageRows($this->DBTag, '*', $where, $orders, $page, $discount, $url);
	}
	
	public function getCouponCount()
	{
		$sql  = 'SELECT merchantId, COUNT(1) total FROM coupon WHERE status = 1 GROUP BY merchantId';
			
		return $this->getHash($this->DBTag, $sql);
	}
	
	public function getHasCoupons()
	{
		$sql  = 'SELECT id, r_couponCount FROM merchant WHERE status = 1 AND r_couponCount > 0';
			
		return $this->getHash($this->DBTag, $sql);
	}
	
	public function getLogo($mercahntId) 
	{
		$sql  = "SELECT b.id id, b.extName extName FROM merchant_images a, upload_files b WHERE a.imageID = b.id AND a.merchantID = $mercahntId";
		$sql .= " ORDER BY a.level LIMIT 1";
		$conn 	= null;
		$oImage	= null;
		
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn);
			if ($row = mysql_fetch_row($rs)) {
				$oImage = new ImageDomain();

				$oImage->setId($row[0]);
				$oImage->setExtName($row[1]);	
			} else {
				$this->message = "get merchant logo data failed by Id. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($conn);
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		
		return $oImage;
	}
	
	public function getSiteThumbnail($mercahntId) 
	{
		$sql  	= "SELECT b.id id, b.extName extName FROM site_images a, site_files b WHERE a.imageID = b.id AND a.merchantID = $mercahntId";
		$sql 	.= " ORDER BY a.level LIMIT 1";
		$conn 	= null;
		$oImage	= null;
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn);
			if ($row = mysql_fetch_row($rs)) {
				$oImage = new ImageDomain();

				$oImage->setId($row[0]);
				$oImage->setExtName($row[1]);				
			} else {
				$this->message = "get merchant logo data failed by Id. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($conn);
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		return $oImage;
	}
	
	public function getAttributes($oMerchant)
	{
		$arrback 	= array();
		$conn 		= null;
		$sql  		= "SELECT sourceId, attributeId, attributeValue FROM merchant_attributes WHERE merchantId = " . $oMerchant->getId();
		
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn);
			while ($row = mysql_fetch_row($rs)) {
				$oAttribute = new AttributeDomain();

				$oAttribute->setSourceId($row[0]);
				$oAttribute->setAttributeId($row[1]);
				$oAttribute->setAttributeValue($row[2]);
				
				$arrback[$row[0] . '_' . $row[1]] = $oAttribute;
			}
			
			$oMerchant->setAttributes($arrback);
		} catch (Exception $e) {
			$this->message = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		
		return $arrback;
	}
	
	public function getSources($mercahntId)
	{
		$arrback 	= array();
		$conn 		= null;
		$sql  = "SELECT sourceId FROM merchant_source WHERE merchantId = $mercahntId";
		
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn);
			while ($row = mysql_fetch_row($rs)) {
				$arrback[$row[0]] = $row[0];
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		
		return $arrback;
	}
	
	public function getCategoryHash($mercahntId)
	{
		$arrback 	= array(); 
		$conn 		= null;
		$sql  = "SELECT m.categoryId, c.name FROM merchant_category m, category c WHERE m.categoryId = c.id AND m.merchantId = $mercahntId";
		
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn);
			while ($row = mysql_fetch_row($rs)) {
				$arrback[$row[0]] = $row[1];
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		return $arrback;
	}
	
	
	
		
	
}
?>
